const mysql = require('mysql2/promise');

let pool;

const connectDB = async () => {
  try {
    pool = mysql.createPool({
      host: process.env.DB_HOST || 'localhost',
      port: process.env.DB_PORT || 3306,
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      database: process.env.DB_NAME || 'watermark_remover',
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0,
      acquireTimeout: 60000,
      timeout: 60000,
      reconnect: true
    });

    // 测试连接
    await pool.getConnection();
    
    // 创建表结构
    await createTables();
    
    return pool;
  } catch (error) {
    console.error('数据库连接失败:', error);
    throw error;
  }
};

const createTables = async () => {
  const connection = await pool.getConnection();
  
  try {
    // 用户表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        avatar VARCHAR(255),
        role ENUM('free', 'premium', 'enterprise') DEFAULT 'free',
        monthly_usage INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);

    // 处理任务表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS tasks (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT,
        task_type ENUM('image', 'video') NOT NULL,
        original_file VARCHAR(255) NOT NULL,
        processed_file VARCHAR(255),
        status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
        progress INT DEFAULT 0,
        error_message TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      )
    `);

    // 用户订阅表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS subscriptions (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT,
        plan_type ENUM('monthly', 'yearly', 'enterprise') NOT NULL,
        status ENUM('active', 'expired', 'cancelled') DEFAULT 'active',
        start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        end_date TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      )
    `);

    // 处理历史表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS processing_history (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT,
        task_id INT,
        file_name VARCHAR(255) NOT NULL,
        file_size BIGINT NOT NULL,
        processing_time INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
      )
    `);

    console.log('✅ 数据库表创建成功');
  } catch (error) {
    console.error('❌ 创建数据库表失败:', error);
    throw error;
  } finally {
    connection.release();
  }
};

const getConnection = () => {
  if (!pool) {
    throw new Error('数据库未连接');
  }
  return pool;
};

module.exports = {
  connectDB,
  getConnection
}; 